import pymysql
from django.test import TestCase

# Create your tests here.


# from pymongo import MongoClient
#
# mongo_client = MongoClient('mongodb://localhost:27017')
#
# db = mongo_client.db_name
# c = db.c_name

# import mongoengine
# conn = mongoengine.connect("modb")
# print(conn)


import re
import pymysql

# 通过正则识别手机号方法
def check_phone(value):
    if len(value.rstrip()) >= 11:
        value_format = value.rstrip()[-11:]
        try:
            int(value_format)
            phone_pattern = '^[1](([3][0-9])|([4][5-9])|([5][0-3,5-9])|([6][5,6])|([7][0-8])|([8][0-9])|([9][1,8,9]))[0-9]{8}$'
            if re.match(phone_pattern, value_format):
                return '手机号', value
            else:
                return '非法输入',value
            # print('手机号', value)
        except ValueError:
            return '非法输入',value
    else:
        return '非法输入',value

class DB:
    def __init__(self,ip,username,password):
        self.ip = ip
        self.username = username
        self.password = password
        self.db = pymysql.connect(host=self.ip,user=self.username,password=self.password)
        self.cursor = self.db.cursor()

    # 通过schemata获取所有数据库名称
    def get_database(self):
        self.cursor.execute("SELECT schema_name from information_schema.schemata")
        database_list = self.cursor.fetchall()
        result = []
        for line in database_list:
            if line[0] not in ['information_schema','mysql','performance_schema']:
                result.append(line[0])
        return result

    # 获取数据库对应表名
    def get_table(self,database):
        self.cursor.execute("select table_name from information_schema.tables where table_schema='%s'" % database)
        table_list = self.cursor.fetchall()
        result = []
        for line in table_list:
            result.append(line[0])
        return result

    # 获取字段名
    def get_column(self,database,table):
        self.cursor.execute("select column_name from information_schema.columns where table_schema='%s' and table_name='%s'" %(database,table))
        column_list = self.cursor.fetchall()
        result = []
        for line in column_list:
            result.append(line[0])
        return result

    # 获取字段内容
    def get_content(self,database,table,column):
        # 增加采样数据，抽取3条数据进行验证
        self.cursor.execute("select %s from %s.%s limit 3" %(column,database,table))
        contents = self.cursor.fetchall()
        if contents:
            return contents
        else:
            return tuple()

if __name__ == '__main__':
    db = DB('127.0.0.1','root','root')
    databases = db.get_database()
    for database in databases:
        tables = db.get_table(database)
        for table in tables:
            columns = db.get_column(database,table)
            for column in columns:
                querydata = db.get_content(database,table,column)
                print(type(querydata),querydata)
                for data in querydata:
                    print(type(data),data)
                    data_str = str(data[0])
                    print(type(data_str),data_str)
                    result = [database,table,column,check_phone(data_str)]
                    result_str = str(result) + "\r\n"
                    with open('db_classify_result.txt','a+',encoding='utf-8') as f:
                        f.write(result_str)










